﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using KPIS.DBM;
using System.Configuration;
using KPIS.GERP.GAMS.MODEL;
using System.Data;

namespace KPIS.GERP.GAMS.DAL
{
    public class D_ACM_Calculate_Depreciation
    {
        DBManager dbManager = new DBManager(SystemInfo._DataProvider, ConfigurationSettings.AppSettings["ConnectionString"].ToString());
        ConvertNullable cn = new ConvertNullable();

        public DataTable GetAssets(DateTime? CalDate = null)
        {
            string strSQL = "";
            DataTable dtb;

            try
            {
                dbManager.Open();
                dbManager.CreateParameters(1);
                dbManager.AddParameters(0, "@CalDate", CalDate);

                strSQL = @"
select a.*
, dep.AccumDepreAmount, grp.AssetAccountId, grp.CumdepreAccountId, grp.DepreAccountId
from IcmAsset a
left join IcmMovementHistory move on a.AssetId = move.AssetId
inner join IcmMasResourceItem item on a.ResItemId = item.ResItemId
inner join IcmMasResourceSubGroup sub on item.ResSubGrpId = sub.ResSubGrpId
inner join IcmMasResourceGroup grp on sub.ResGrpId = grp.ResGrpId
left join (select AssetId, sum(coalesce(DepAmount, 0)) as AccumDepreAmount
from IcmAssetDepreciation
where RecordStatus <> 'D'
group by AssetId
) dep on a.AssetId = dep.AssetId
cross join (select max(CalculatedDate) as CalculatedDate from IcmAssetDepreciation where RecordStatus <> 'D'
) dep2
where a.RecordStatus <> 'D'
and a.IsLowerAsset = 0
"
+ (CalDate.HasValue ? @"
or (move.RecordStatus = 'A'
and move.MovementType = 'Dispose'
and move.CreatedWhen >= dep2.CalculatedDate
and move.CreatedWhen <= @CalDate
)" : "")
   
+ @"
order by a.AssetCode";
                dtb = dbManager.ExecuteDataSet(CommandType.Text, strSQL).Tables[0];
            }
            catch (Exception ex)
            {
                throw (ex);
            }
            finally
            {
                dbManager.Dispose();
            }

            return dtb;
        }

        public DataTable CalDepre(string AssetId, DateTime CalDate)
        {
            string strSQL = "";
            DataTable dtb;

            try
            {
                dbManager.Open();
                dbManager.CreateParameters(1);
                dbManager.AddParameters(0, "@AssetId", AssetId);

                strSQL = @"
select a.AssetId
, a.AssetDate, a.Price, a.LifeYear, a.BgSourceId
, grp.AssetAccountId
, grp.DepreAccountId
, grp.CumdepreAccountId
, max(COALESCE(dep.CalculatedDate, a.AssetDate)) as CalculatedDate
, sum(COALESCE(dep.DepAmount, 0)) as DepAmount
from IcmAsset a
left join IcmAssetDepreciation dep on a.AssetId = dep.AssetId
inner join IcmMasResourceItem item on a.ResItemId = item.ResItemId
inner join IcmMasResourceSubGroup sub on item.ResSubGrpId = sub.ResSubGrpId
inner join IcmMasResourceGroup grp on sub.ResGrpId = grp.ResGrpId

where dep.AssetId = @AssetId
and dep.RecordStatus <> 'D'

group by a.AssetId, a.AssetDate, a.Price, a.LifeYear, a.BgSourceId
, grp.AssetAccountId
, grp.DepreAccountId
, grp.CumdepreAccountId";
                dtb = dbManager.ExecuteDataSet(CommandType.Text, strSQL).Tables[0];
            }
            catch (Exception ex)
            {
                throw (ex);
            }
            finally
            {
                dbManager.Dispose();
            }

            return dtb;
        }

    }

    public class Depre
    {
        public string BgmSourceSeq { get; set; }
        public string AccumDepreAccId { get; set; }
        public string DepreAccId { get; set; }
        public double Amount { get; set; }
    }
}
